package net.huashitong.oversee.dao;

import com.sirdc.modules.core.dao.GenericQuery;
import com.sirdc.modules.core.dao.StringDao;
import com.sirdc.modules.core.filter.Paging;
import com.sirdc.modules.utils.ObjectUtils;
import com.sirdc.modules.utils.StringUtils;
import jodd.util.StringUtil;
import net.huashitong.node.entity.NodeNewschedules;
import net.huashitong.oversee.databean.DepartRank;
import net.huashitong.oversee.entity.ScoreSummary;
import net.huashitong.oversee.filter.ScoreSummaryFilter;
import net.huashitong.user.databean.OnLineDatabean;
import org.hibernate.SQLQuery;
import org.hibernate.transform.Transformers;
import org.springframework.stereotype.Repository;

import java.util.List;

@Repository
public class ScoreSummaryDao extends StringDao<ScoreSummary> {

    public List<ScoreSummary> queryByFilter(ScoreSummaryFilter filter) {
        GenericQuery query = createQuery("obj");
        if (StringUtils.isNotBlank(filter.getSummaryDate())) {
            query.append(" and summaryDate =:summaryDate").setParam("summaryDate",
                    filter.getSummaryDate());
        }
        if (StringUtils.isNotBlank(filter.getBranchType())) {
            query.append(" and branchType =:branchType").setParam("branchType",
                    filter.getBranchType());
        }

        //模糊搜索
        if (StringUtils.isNotBlank(filter.getSearchWord())) {
            query.append(" and (obj.userId like:userId or " +
                    "obj.checkObject like:checkObject or " +
                    "obj.checkDept like:checkDept) ").setParam("userId",
                    StringUtils.getLikewords(filter.getSearchWord())).setParam("checkObject",
                    StringUtils.getLikewords(filter.getSearchWord())).setParam("checkDept",
                    StringUtils.getLikewords(filter.getSearchWord()));
        }
        if (ObjectUtils.isNotBlank(filter.getPaging())) {
            if (StringUtils.isBlank(filter.getPaging().getSortCol())) {
                filter.getPaging().setSortCol("rank+0");
            }
        }

        return query.listResult(filter);
    }

    public List<ScoreSummary> queryByFilterNoPage(ScoreSummaryFilter filter) {
        GenericQuery query = createQuery("obj");
        if (StringUtils.isNotBlank(filter.getCheckDept())) {
            query.append(" and checkDept like:checkDept").setParam("checkDept",
                    StringUtils.getLikewords(filter.getCheckDept()));
        }
        if (StringUtils.isNotBlank(filter.getCheckObject())) {
            query.append(" and checkObject like:checkObject").setParam("checkObject",
                    StringUtils.getLikewords(filter.getCheckObject()));
        }
        if (StringUtils.isNotBlank(filter.getSummaryDate())) {
            query.append(" and summaryDate =:summaryDate").setParam("summaryDate",
                    filter.getSummaryDate());
        }
        if (StringUtils.isNotBlank(filter.getUserId())) {
            query.append(" and userId =:userId").setParam("userId",
                    filter.getUserId());
        }
        if (StringUtils.isNotBlank(filter.getBranchType())) {
            query.append(" and branchType =:branchType").setParam("branchType",
                    filter.getBranchType());
        }
        if (ObjectUtils.isNotBlank(filter.getPaging())) {
            if (StringUtils.isBlank(filter.getPaging().getSortCol())) {
                filter.getPaging().setSortCol("rank+0");
            }
        } else {
            query.append(" ORDER BY rank+0 asc");
        }
        return query.listResult();
    }

    public List<ScoreSummary> queryScoreSummaryByIds(List<String> ids) {
        GenericQuery query = createQuery("obj");
        query.append("and obj.sysId in(:ids)").setParamList("ids", ids);
        return query.listResult();
    }


    public List<DepartRank> queryDepartRank(ScoreSummaryFilter filter) {
        StringBuffer sqlcount = new StringBuffer();
        sqlcount.append("SELECT count(check_dept) FROM t_scoresummary S  where 1 = 1 ");
        if (StringUtils.isNotBlank(filter.getBranchType())) {
            sqlcount.append(" and S.branch_type = '" + filter.getBranchType() + "' ");
        }
        //模糊搜索
        if (StringUtils.isNotBlank(filter.getSearchWord())) {
            sqlcount.append("  and (S.check_object like '%" + filter.getSearchWord()
                    + "%' or S.check_dept like '%" + filter.getSearchWord() + "%')");
        }
        sqlcount.append(" group by check_dept ");
        SQLQuery queryCount = getSession().createSQLQuery(sqlcount.toString());
        List resultCount = queryCount.list();

        int total = resultCount.size();
        filter.getPaging().setPageCount(total / filter.getPaging().getPageSize() + 1);
        filter.getPaging().setRecords(total);
        StringBuffer sql = new StringBuffer();
        int fristnum = (filter.getPaging().getCurrentPage() - 1) * filter.getPaging().getPageSize();
//        int endnum = fristnum + filter.getPaging().getPageSize();
        int endnum = 10;
        sql.append("select sys_id sysId,check_dept checkDept," +
                "check_dept checkObject,summary_date summaryDate,avg(score) score " +
                "from t_scoresummary p where 1 = 1 ");
        if (StringUtils.isNotBlank(filter.getBranchType())) {
            sql.append(" and p.branch_type = '" + filter.getBranchType() + "' ");
        }
        //模糊搜索
        if (StringUtils.isNotBlank(filter.getSearchWord())) {
            sql.append("  and (p.check_object like '%" + filter.getSearchWord()
                    + "%' or p.check_dept like '%" + filter.getSearchWord() + "%')");
        }
        if ("asc".equals(filter.getPaging().getSortVal())) {
            sql.append("group by check_dept order by avg(score) desc ");
        } else if ("desc".equals(filter.getPaging().getSortVal())) {
            sql.append("group by check_dept order by avg(score) asc ");
        } else {
            sql.append("group by check_dept order by avg(score) desc ");
        }
        sql.append("limit " + fristnum + "," + endnum);
        SQLQuery query = getSession().createSQLQuery(sql.toString());
        query.setResultTransformer(Transformers.aliasToBean(DepartRank.class));
        List<DepartRank> list = (List<DepartRank>) query.list();
        return list;
    }


    public List<DepartRank> queryDepartRankNoPage(ScoreSummaryFilter filter) {

        StringBuffer sql = new StringBuffer();
        sql.append("select sys_id sysId,check_dept checkDept," +
                "check_dept checkObject,summary_date summaryDate,avg(score) score " +
                "from t_scoresummary p where 1 = 1 ");
        if (StringUtils.isNotBlank(filter.getBranchType())) {
            sql.append(" and branch_type = '" + filter.getBranchType() + "' ");
        }
        if (StringUtils.isNotBlank(filter.getCheckDept())) {
            sql.append("and check_dept like " + StringUtils.getLikewords(filter.getCheckDept()));
        }
        if (StringUtils.isNotBlank(filter.getCheckObject())) {
            sql.append("and check_object like " + StringUtils.getLikewords(filter.getCheckObject()));
        }
        if (ObjectUtils.isNotBlank(filter.getPaging())) {
            if ("asc".equals(filter.getPaging().getSortVal())) {
                sql.append("group by check_dept order by avg(score) desc ");
            } else if ("desc".equals(filter.getPaging().getSortVal())) {
                sql.append("group by check_dept order by avg(score) asc ");
            } else {
                sql.append("group by check_dept order by avg(score) desc ");
            }
        } else {
            sql.append("group by check_dept order by avg(score) desc ");
        }
        SQLQuery query = getSession().createSQLQuery(sql.toString());
        query.setResultTransformer(Transformers.aliasToBean(DepartRank.class));
        List<DepartRank> list = (List<DepartRank>) query.list();
        return list;
    }


    public List<DepartRank> queryDepartRankNoPageInTime(ScoreSummaryFilter filter) {

        StringBuffer sql = new StringBuffer();
        sql.append("select sys_id sysId,check_dept checkDept," +
                "check_dept checkObject,summary_date summaryDate,avg(score) score " +
                "from t_scoresummary_temp p where 1 = 1 ");
        if (StringUtils.isNotBlank(filter.getBranchType())) {
            sql.append(" and branch_type = '" + filter.getBranchType() + "' ");
        }
        if (StringUtils.isNotBlank(filter.getCheckDept())) {
            sql.append("and check_dept like " + StringUtils.getLikewords(filter.getCheckDept()));
        }
        if (StringUtils.isNotBlank(filter.getCheckObject())) {
            sql.append("and check_object like " + StringUtils.getLikewords(filter.getCheckObject()));
        }
        if (ObjectUtils.isNotBlank(filter.getPaging())) {
            if ("asc".equals(filter.getPaging().getSortVal())) {
                sql.append("group by check_dept order by avg(score) desc ");
            } else if ("desc".equals(filter.getPaging().getSortVal())) {
                sql.append("group by check_dept order by avg(score) asc ");
            } else {
                sql.append("group by check_dept order by avg(score) desc ");
            }
        } else {
            sql.append("group by check_dept order by avg(score) desc ");
        }
        SQLQuery query = getSession().createSQLQuery(sql.toString());
        query.setResultTransformer(Transformers.aliasToBean(DepartRank.class));
        List<DepartRank> list = (List<DepartRank>) query.list();
        return list;
    }
}
